from unittest.main import main
import requests
import psycopg2
import psycopg2.extras
from setting import *

class Pg():

    def __init__(self) -> None:

        self.HOST = HOST#在setting文件中设置
        self.dsn = f"host={PS_HOST} port={PS_PORT} user={PS_USER} password={PS_PASSWORD} dbname={PS_DATABASE}"
        self.conn = psycopg2.connect(self.dsn)
        self.cur = self.conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)

        self.insert_brand()
        self.insert_class_parent()
        self.insert_class_son()
        self.insert_spu()
        self.insert_spu_attribute()
        self.insert_sku()
        self.insert_sku_attribute()
        self.close()

    def close(self):
        self.cur.close()
        self.conn.close()

    def insert_brand(self):

        # brand_list = [("立邦",), ("汉高百得",), ("鲁班神工",), ("晨阳水漆",), ("牛元",)]

        sql = "insert into brand(brand_name) values ('立邦') returning id"
        
        self.cur.execute(sql)
        self.brand_id = self.cur.fetchone()['id']
        self.conn.commit()

    def insert_class_parent(self):

        sql = "insert into class(class_name) values ('油漆') returning id"
        self.cur.execute(sql)
        self.class_parent_id = self.cur.fetchone()['id']
        self.conn.commit()

    def insert_class_son(self):

        sql = f"insert into class(class_name, parent_id) values ('乳胶油漆', {self.class_parent_id}) returning id"
        self.cur.execute(sql)
        self.class_son_id = self.cur.fetchone()['id']
        self.conn.commit()

    def insert_spu(self):
        sql = f"insert into spu(class_id, spu_name) values ('{self.class_son_id}', '乳胶漆') returning id"
        self.cur.execute(sql)
        self.spu_id = self.cur.fetchone()['id']
        self.conn.commit()

    def insert_spu_attribute(self):
        sql = f"insert into spu_attribute(spu_id, attribute_key, attribute_value) values ('{self.spu_id}', '原厂','是') returning id"
        self.cur.execute(sql)
        self.spu_attribute_id_1 = self.cur.fetchone()['id']

        sql = f"insert into spu_attribute(spu_id, attribute_key, attribute_value) values ('{self.spu_id}', '原厂','非') returning id"
        self.cur.execute(sql)
        self.spu_attribute_id_2 = self.cur.fetchone()['id']

        sql = f"insert into spu_attribute(spu_id, attribute_key, attribute_value) values ('{self.spu_id}', '容量','20L') returning id"
        self.cur.execute(sql)
        self.spu_attribute_id_3 = self.cur.fetchone()['id']

        self.conn.commit()

    def insert_sku(self):
        sql = f"insert into sku(spu_id, brand_id) values ('{self.spu_id}', '{self.brand_id}') returning id"
        self.cur.execute(sql)
        self.sku_id_1 = self.cur.fetchone()['id']

        sql = f"insert into sku(spu_id, brand_id) values ('{self.spu_id}', '{self.brand_id}') returning id"
        self.cur.execute(sql)
        self.sku_id_2 = self.cur.fetchone()['id']
        
        self.conn.commit()
        
    def insert_sku_attribute(self):
        sql = f"insert into sku_attribute(sku_id, attribute_id) values ('{self.sku_id_1}', '{self.spu_attribute_id_1}') returning id"
        self.cur.execute(sql)
        self.sku_attribute_id = self.cur.fetchone()['id']

        sql = f"insert into sku_attribute(sku_id, attribute_id) values ('{self.sku_id_2}', '{self.spu_attribute_id_2}') returning id"
        self.cur.execute(sql)
        self.sku_attribute_id = self.cur.fetchone()['id']

        sql = f"insert into sku_attribute(sku_id, attribute_id) values ('{self.sku_id_2}', '{self.spu_attribute_id_3}') returning id"
        self.cur.execute(sql)
        self.sku_attribute_id = self.cur.fetchone()['id']
        self.conn.commit()
if __name__ == "__main__":

    Pg()
